﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.IO.Ports;
using MySql.Data.MySqlClient;
using Microsoft.Office.Interop.Excel;

namespace StuMana.UI
{
    public partial class Activities : Form
    {
        // export excel
        Services.ExcelInterface excel = null;

        // datagridview variables
        public static Services.ConnectionController retrieveData = new Services.ConnectionController();
        private string query;
        string[] columnName = new string[6];
        string[] columnNameReal = new string[6];
        int time1;
        int time2;
        int page=0;
        int maxpage;
        static int rowperpage=20;
        List<string>[] list;

        //textbox tagID variables
        private int action = 0;

        // excel export

        public Activities()
        {
            InitializeComponent();
            activities_initial();
        }

        // getPanel for mainform
        public Panel getPanel(int x, int y)
        {
            p_Activities.Location = new System.Drawing.Point(x, y);
            return p_Activities;
        }

        // initial values
        private void activities_initial()
        {

            datagrid_config();
            
            refreshDataOnDataGridView(dataGridView1);
        }

        /*
         * Engineer: NhanHN
         * Type: 
         * function: load data from database to gatagridview
         */
        public void refreshDataOnDataGridView(DataGridView datagrid)
        {           
            
            try
            {
                datagrid.Rows.Clear();

                //string query = "SELECT * FROM manager where username=@username and pass=@pass";
                MySqlCommand cmd = new MySqlCommand(query, retrieveData.getMySqlConnection());

                // Name of columns
                list = retrieveData.selectStringType(cmd, columnName, time1, time2);
                maxpage = list[0].Count / rowperpage;
                cmd = null;

                page = 0;
                if (page == maxpage)
                    bt_next.Enabled = false;                
                bt_previous.Enabled = false;
                
                viewdata(datagrid);

                if (!retrieveData.CloseConnection())
                {
                    MessageBox.Show("Connection closing failed.", "Error");
                }
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
                MessageBox.Show("Database error: \n" + ex.Message, "Error");
            }

        }

        // view data to restrict datagridview size
        private void viewdata(DataGridView datagrid)
        {
            int offset = page * rowperpage;
            pages.Text = (page+1).ToString() + "/" + (maxpage+1).ToString();
            // row elements
            datagrid.Columns.Clear();

            for (int i = 0; i < columnName.Length; i++)
            {
                datagrid.Columns.Add(columnName[i].ToString(), columnNameReal[i].ToString());
            }

            if (list != null)
            {
                int rows = 0;
                for (int j = offset; j < offset+rowperpage; j++)
                {
                    if (rows < list[0].Count-page*rowperpage)
                    {
                        datagrid.Rows.Add();
                        datagrid.Rows[rows].HeaderCell.Value = (page * rowperpage+rows + 1).ToString();
                        for (int i = 0; i < columnName.Length; i++)
                        {
                            datagrid.Rows[rows].Cells[i].Value = list[i][j];
                        }
                        rows++;
                    }
                    else return;
                }
            }
        }
        // Forming the query from checkboxes
        private void datagrid_config(string tagID=null, string cbb_class=null)
        {
            int i=1;
            Array.Resize<string>(ref columnName, 7);
            Array.Resize<string>(ref columnNameReal, 7);
            Array.Clear(columnName, 0, columnName.Length);
            Array.Clear(columnNameReal, 0, columnNameReal.Length);
            columnName[0] = "TagID";
            columnNameReal[0] = "TagID";
            query = "SELECT studentinfo.TagID,";
            time1 = -1;
            time2 = -1;
            if (checkBox1.Checked){
                query += "studentinfo.Name,";
                columnName[i] = "Name";
                columnNameReal[i] = "Name";
                i++;
            }
            if (checkBox2.Checked){
                query += "studentinfo.BirthDay,";
                columnName[i] = "BirthDay";
                columnNameReal[i] = "BirthDay";  
                time1 = i;
                i++;
            }
            if (checkBox3.Checked){
                query += "studentinfo.Gender,";
                columnName[i] = "Gender";
                columnNameReal[i] = "Gender";
                i++;
            }
            if (checkBox4.Checked){
                query += "studentinfo.Class,";
                columnName[i] = "Class";
                columnNameReal[i] = "Class";
                i++;
            }
            if (checkBox5.Checked){
                query += "time_inout.Time,";
                columnName[i] = "Time";
                columnNameReal[i] = "Time";
                time2 = i;
                i++;
            }       

            if (checkBox6.Checked){
                query += "time_inout.inout,";
                columnName[i] = "inout";
                columnNameReal[i] = "inout";
                i++;
            }
            query = query.Remove(query.Length - 1, 1);
            query += " FROM studentinfo join time_inout ON studentinfo.TagID = time_inout.TagID";
            if (tb_class.Text != "" && tb_class != null)
            {
                query += " && studentinfo.Class = '" + tb_class.Text + "'";
            }
            if (tagID != "" && tagID != null)
            {
                query +=" && studentinfo.TagID LIKE '"+tagID+"%'";
                }
            Array.Resize<string>(ref columnName, i);
            Array.Resize<string>(ref columnNameReal, i);
        }

        // checked-event for all checkboxes
        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            /*
            if(tb_tagid.Text.Length > 0)
                datagrid_config(tb_tagid.Text);
            else
                datagrid_config();
            refreshDataOnDataGridView(dataGridView1, 2);
             */
        }

        // tb1 keydown to decide whether acting
        private void textBox1_KeyDown(object sender, KeyEventArgs e)
        {
            if ((e.KeyValue >= 48 && e.KeyValue <= 57)||(e.KeyValue >= 96 && e.KeyValue <= 105))
            {
                action = 1;
                ts_label.Text = "None";
            }
            else if (e.KeyValue == 46)
            {
                action = 1;
            }
            else if (e.KeyValue != 8)
            {
                action = 2;
                ts_label.Text = "Number only";
            }
        }

        // tagID textbox type
        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            
            switch (action)
            {

                //remove last input character
                case 2:
                    remove_character();
                    break;
                default:                    
                    break;
            }
             
        }

        // remove the last character input
        private void remove_character()
        {
            int temp = tb_tagid.SelectionStart;
            if (temp > 0)
            {
                tb_tagid.Text = tb_tagid.Text.Remove(temp - 1, 1);
                tb_tagid.SelectionStart = temp - 1;
            }
        }

        //set status to ts_connect
        public void setStatus(string stt)
        {
            ts_connect.Text = stt;
        }

        //export to excel
        private void button1_Click(object sender, EventArgs e)
        {
            
            excel = new Services.ExcelInterface(list,columnName,time1,2,time2,1);
            
        }

        private void bt_search_Click(object sender, EventArgs e)
        {
            bt_next.Enabled = true;
            datagrid_config(tb_tagid.Text);
            refreshDataOnDataGridView(dataGridView1);
        }

        // acting of bt_next clicked
        private void bt_next_Click(object sender, EventArgs e)
        {
            page++;
            bt_previous.Enabled = true;
            if (page >= maxpage)
                bt_next.Enabled = false;
            viewdata(this.dataGridView1);
        }

        // acting of bt_previous clicked
        private void bt_previous_Click(object sender, EventArgs e)
        {
            page--;
            bt_next.Enabled = true;
            if (page <= 0)
                bt_previous.Enabled = false;
            viewdata(this.dataGridView1);
        }

    }
}